﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using DevExpress.Web.ASPxEditors;
using DevExpress.Web.ASPxGridView;

/// <summary>
/// Summary description for DatabaseConnect
/// </summary>
public class DatabaseConnect
{

    string connectionString;

    #region "Contrustor"

    public DatabaseConnect()
    {
        this.connectionString = ConfigurationManager.ConnectionStrings["stringConnection"].ToString();
       // this.connectionString = ConfigurationManager.ConnectionStrings["connectionStringServer"].ToString();
    }
    public DatabaseConnect(string connectionString)
	{
        this.connectionString = connectionString;
	}
    #endregion

    #region "Method"
    private string AddParameters(SqlCommand CommandObject, SqlParameter[] CommandParameters)
    {
        string ReturnValue = string.Empty;
        int i;
        if (CommandParameters != null)
        {
            for (i = 0; i <= CommandParameters.Length - 1; i++)
            {
                CommandObject.Parameters.Add(CommandParameters[i]);
                if (CommandParameters[i].Direction == ParameterDirection.Output)
                {
                    ReturnValue = CommandParameters[i].ParameterName;
                }
            }
        }
        return ReturnValue;
    }

    #endregion

    #region "GetSeleCommand"
    public DataTable GetSelectCommand(string selectCommand) 
    {
        SqlConnection connect = new SqlConnection();
        connect.ConnectionString = this.connectionString;

        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = selectCommand;
        cmd.Connection = connect;

        SqlDataAdapter data = new SqlDataAdapter();
        data.SelectCommand = cmd;

        DataTable dt = new DataTable();
        data.Fill(dt);

        return dt; 
    }

    public DataTable GetSelectCommand(string procedureName, SqlParameter[] parameter)
    {
        DataTable table = new DataTable();
        SqlConnection connect = new SqlConnection(this.connectionString);

        SqlCommand command = new SqlCommand(procedureName, connect);
        command.CommandType = CommandType.StoredProcedure;        
        //Add Parameter
        AddParameters(command, parameter);

        SqlDataAdapter adapter = new SqlDataAdapter(command);
        adapter.Fill(table);

        command.Dispose();
        return table;
    }
    #endregion

    
#region "ExecuteQuery Method"
    public void ExecuteQuery(string sql) // sql: Insert, Update, Delete command in SQL Server
    { 
        SqlConnection connect = new SqlConnection();
        connect.ConnectionString = this.connectionString;
        
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = sql;
        cmd.Connection = connect;

        connect.Open();

        cmd.ExecuteNonQuery();

        connect.Close();
    }

    public void ExecuteQuery(string procudureName, SqlParameter[] parameter)
    {
        SqlConnection connect = new SqlConnection();
        connect.ConnectionString = this.connectionString;

        SqlCommand cmd = new SqlCommand(procudureName, connect);
        cmd.CommandType = CommandType.StoredProcedure;

        // Add parameter
        this.AddParameters(cmd, parameter);

        connect.Open();

        cmd.ExecuteNonQuery();

        connect.Close();
    }
    #endregion

    #region "FillCombox Method"
    public void FillCombobox(string selectCommand, ASPxComboBox combobox)
    {
        DataTable tb =  this.GetSelectCommand(selectCommand);
        combobox.DataSource = tb;
        combobox.ValueField = tb.Columns[0].ToString();
        combobox.TextField = tb.Columns[1].ToString();
        combobox.DataBind();
        //combobox.SelectedIndex = 0;
    }
    #endregion

    #region "FillGridView Method"
    public void FillGridView(string selectCommand, ASPxGridView gridview)
    { 
        DataTable tb =  this.GetSelectCommand(selectCommand);
        gridview.DataSource = tb;
       // gridview.PageIndex = 0;
        gridview.DataBind();
    }

    public void FillGridView(string procedureName, SqlParameter[] parameter, ASPxGridView gridview)
    {
        DataTable tb = this.GetSelectCommand(procedureName, parameter);
        gridview.DataSource = tb.DefaultView;
        gridview.DataBind();
    }
    #endregion
}